﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Data;
using System.Windows.Forms;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.ComponentModel;
using System.Drawing.Text;
using WP.DB;
using System.Runtime.InteropServices;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using System.Drawing.Printing;
using WP.Commons.ToExcelProc;
namespace WP.Commons
{
    public  class BOMToExcel
    {
        public enum BOMType
        {
            CategorySetBOM = 0,
            ProductBOM,
            BOMRev,
            BOMSales
        }

        public static void ToExcel_BOM(DBAccess dbcon, BOMType bomType, DataRow drHdr)
        {
            string strBOMSQL = "",strLabourCostSQL = "",strCategoryName = "",strSetName = "", strSalesProductName="";
            string strProductName = "", strVersion = "";
            if (bomType == BOMType.CategorySetBOM)
            {
                strCategoryName = CvtFunc.ToString(drHdr["CategoryName"]);
                strSetName = CvtFunc.ToString(drHdr["SetName"]);
                strBOMSQL = "Select WKName as 工序名称,WKRemark as 工序描述,ItemNo as 材料代码,ItemName as 材料名称,Price as 单价,PriceUnit as 单位,WastageRate as 损耗率 from rmCategorySetBOMDetail where CategoryName='" + strCategoryName + "' and SetName='" + strSetName + "' order by RowSeqNo,SeqNo,WKName desc";
                strLabourCostSQL = " Select WKName  as 工序名称,WKRemark as 工序描述,IsKeyWK as 关键工序,A.ItemNo  as 材料代码,B.ItemName as 材料名称,CalUnit as 计件单位,CutType as 分切系数,StandardQty as [额定产量(10小时)],StandardWage as 固定工资,StandardWage26 as [标准工资(26天)],WorkCost as 工时费用标准,A.Remark as 备注  from dbo.rmCategorySetBOMLabourCost A ";
                strLabourCostSQL += " inner join iminvmaster B on a.ItemNo=B.itemNo where CategoryName ='" + strCategoryName + "' and SetName='" + strSetName + "' order by A.RowSeqNo";
            }
            else if (bomType == BOMType.ProductBOM)
            {
                strCategoryName = CvtFunc.ToString(drHdr["CategoryName"]);
                strSetName = CvtFunc.ToString(drHdr["SetName"]);
                strProductName = CvtFunc.ToString(drHdr["ProductName"]);
                strBOMSQL = "Select WKName as 工序名称,WKRemark as 工序描述,ItemNo as 材料代码,ItemName as 材料名称,Price as 单价,PriceUnit as 单位,Qty as 定额数量,WastageRate as 损耗率,DetailCost as 定额成本,TotalCost as 总单价 from rmProductBOMDetail where CategoryName='" + strCategoryName + "' and SetName='" + strSetName + "' and ProductName='"+ strProductName  +"' order by RowSeqNo,SeqNo,WKName desc";
                strLabourCostSQL = " Select WKName  as 工序名称,WKRemark as 工序描述,IsKeyWK as 关键工序,A.ItemNo  as 材料代码,B.ItemName as 材料名称,CalUnit as 计件单位,CutType as 分切系数,workQty as 总数量,TotalQty as 计量数量,StandardQty as [额定产量(10小时)],spendhours as 耗用工时,StandardWage as 固定工资,StandardWage26 as [标准工资(26天)],unitcost as 计件工价,WorkCost as 工时费用标准,DetailTotWorkCost as 工时费用,A.Remark as 备注  from dbo.rmProductBOMLabourCost A ";
                strLabourCostSQL += " inner join iminvmaster B on a.ItemNo=B.itemNo where CategoryName ='" + strCategoryName + "' and SetName='" + strSetName + "' and ProductName='" + strProductName + "' and VersionNo='"+ strVersion  +"' order by A.RowSeqNo";
            }
            else if (bomType == BOMType.BOMRev)
            {
                strCategoryName = CvtFunc.ToString(drHdr["CategoryName"]);
                strSetName = CvtFunc.ToString(drHdr["SetName"]);
                strProductName = CvtFunc.ToString(drHdr["ProductName"]);
                strVersion = CvtFunc.ToString(drHdr["VersionNo"]);
                strBOMSQL = "Select WKName as 工序名称,WKRemark as 工序描述,ItemNo as 材料代码,ItemName as 材料名称,Price as 单价,PriceUnit as 单位,Qty as 定额数量,WastageRate as 损耗率,DetailCost as 定额成本,TotalCost as 总单价 from dbo.rmProductBOMDetailRev where CategoryName='" + strCategoryName + "' and SetName='" + strSetName + "' and ProductName='"+ strProductName +"' and VersionNo='"+ strVersion +"' order by RowSeqNo,SeqNo,WKName desc";
                strLabourCostSQL = " Select WKName  as 工序名称,WKRemark as 工序描述,IsKeyWK as 关键工序,A.ItemNo  as 材料代码,B.ItemName as 材料名称,CalUnit as 计件单位,CutType as 分切系数,workQty as 总数量,TotalQty as 计量数量,StandardQty as [额定产量(10小时)],spendhours as 耗用工时,StandardWage as 固定工资,StandardWage26 as [标准工资(26天)],unitcost as 计件工价,WorkCost as 工时费用标准,DetailTotWorkCost as 工时费用,A.Remark as 备注  from dbo.rmProductBOMLabourCostRev A ";
                strLabourCostSQL += " inner join iminvmaster B on a.ItemNo=B.itemNo where CategoryName ='" + strCategoryName + "' and SetName='" + strSetName + "' and ProductName='"+ strProductName  +"' and VersionNo='"+ strVersion +"' order by A.RowSeqNo";
            }
            else if (bomType == BOMType.BOMSales)
            {
                strCategoryName = CvtFunc.ToString(drHdr["CategoryName"]);
                strSetName = CvtFunc.ToString(drHdr["SetName"]);
                strProductName = CvtFunc.ToString(drHdr["ProductName"]);
                strSalesProductName = CvtFunc.ToString(drHdr["SalesProductName"]);
                strVersion = CvtFunc.ToString(drHdr["VersionNo"]);
                strBOMSQL = "Select WKName as 工序名称,WKRemark as 工序描述,ItemNo as 材料代码,ItemName as 材料名称,Price as 单价,PriceUnit as 单位,Qty as 定额数量,WastageRate as 损耗率,DetailCost as 定额成本,TotalCost as 总单价 from dbo.rmProductBOMDetailSales where CategoryName='" + strCategoryName + "' and SetName='" + strSetName + "' and ProductName='" + strProductName + "' and VersionNo='" + strVersion + "' order by RowSeqNo,SeqNo,WKName desc";
                strLabourCostSQL = " Select WKName  as 工序名称,WKRemark as 工序描述,IsKeyWK as 关键工序,A.ItemNo  as 材料代码,B.ItemName as 材料名称,CalUnit as 计件单位,CutType as 分切系数,workQty as 总数量,TotalQty as 计量数量,StandardQty as [额定产量(10小时)],spendhours as 耗用工时,StandardWage as 固定工资,StandardWage26 as [标准工资(26天)],unitcost as 计件工价,WorkCost as 工时费用标准,DetailTotWorkCost as 工时费用,A.Remark as 备注  from dbo.rmProductBOMLabourCostSales A ";
                strLabourCostSQL += " inner join iminvmaster B on a.ItemNo=B.itemNo where CategoryName ='" + strCategoryName + "' and SetName='" + strSetName + "' and ProductName='" + strProductName + "' and VersionNo='" + strVersion + "' order by A.RowSeqNo";
            }
            DataView dvBOM = dbcon.GetDataTable(strBOMSQL).DefaultView;
            DataView dvLabourCost = dbcon.GetDataTable(strLabourCostSQL).DefaultView;
            DataGridViewToExcel_BOM(bomType, drHdr, dvBOM, dvLabourCost); 
        }

        public static void DataGridViewToExcel_BOM(BOMType bomType, DataRow drHdr, DataView dvBOM , DataView dvLabourCost)
        {
            if (dvBOM == null || dvLabourCost == null || drHdr ==null ) return;

            Excel.Application xlApplication = new Excel.Application();
            Excel.Workbooks xlBooks = (Excel.Workbooks)xlApplication.Workbooks;
            xlBooks = (Excel.Workbooks)xlApplication.Workbooks;
            Excel._Workbook ThisWorkbook = (Excel._Workbook)(xlBooks.Add(Missing.Value));

            Excel.Worksheet xlSheet = null;

            if (ThisWorkbook.ActiveSheet != null)
            {
                xlSheet = (Excel.Worksheet)ThisWorkbook.ActiveSheet;
                xlSheet.Name = "类别系列";
                xlSheet.Cells[2, 1] = "类别名称";
                xlSheet.Cells[2, 2] = CvtFunc.ToString ( drHdr["CategoryName"]);
                xlSheet.Cells[3, 1] = "系列名称";
                xlSheet.Cells[3, 2] = CvtFunc.ToString ( drHdr["SetName"]);                    
            }
            if (bomType == BOMType.ProductBOM || bomType == BOMType.BOMRev )
            {
                xlSheet.Cells[4, 1] = "产成品名称";
                xlSheet.Cells[4, 2] = CvtFunc.ToString(drHdr["ProductName"]);                    
            }
            else if (bomType == BOMType.BOMSales)
            {
                xlSheet.Cells[4, 1] = "产成品名称/销售产品名称";
                xlSheet.Cells[4, 2] = CvtFunc.ToString(drHdr["ProductName"]) + "/" + CvtFunc.ToString(drHdr["SalesProductName"]);
            }
            if (bomType == BOMType.BOMRev)
            {
                xlSheet.Cells[5, 1] = "版本号";
                xlSheet.Cells[5, 2] = CvtFunc.ToString(drHdr["VersionNo"]);                    
            }
            xlSheet = (Excel.Worksheet)ThisWorkbook.Worksheets.Add(Type.Missing, ThisWorkbook.ActiveSheet, Type.Missing, Type.Missing);
            xlSheet.Name = "BOM";
            
            for (int i = 0; i < dvBOM.Count; i++)
            {
                int x = 0;
                for (int j = 0; j < dvBOM.Table.Columns.Count ; j++)
                {               
                    x++;
                    if (i == 0)
                    {
                        xlSheet.Cells[1, x] = dvBOM.Table.Columns[j].ColumnName;
                    }
                    xlSheet.Cells[i + 2, x] = dvBOM[i][j];                    
                }
            }
            xlSheet.Columns.AutoFit();

            xlSheet = (Excel.Worksheet)ThisWorkbook.Worksheets.Add(Type.Missing, ThisWorkbook.ActiveSheet, Type.Missing, Type.Missing);
            xlSheet.Name = "项目分摊";
            for (int i = 0; i < dvLabourCost.Count; i++)
            {
                int x = 0;
                for (int j = 0; j < dvLabourCost.Table.Columns.Count; j++)
                {
                    x++;
                    if (i == 0)
                    {
                        xlSheet.Cells[1, x] = dvLabourCost.Table.Columns[j].ColumnName;
                    }
                    xlSheet.Cells[i + 2, x] = dvLabourCost[i][j];
                }
            }
            xlSheet.Columns.AutoFit();
            xlApplication.Visible = true;
        }

    }
}
